﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using EpiNEXT.Services;
using EpiNEXT.Services.Fields;

namespace EpiNEXT.Data
{
    /// <summary>
    /// Concrete type for Microsoft SQL Server databases
    /// </summary>
    public class MicrosoftSqlDriver : SqlDriverBase
    {
        #region Members
        private List<IField> _ignoreFields = new List<IField>();
        #endregion // Members

        #region Properties
        /// <summary>
        /// Whether or not the data driver is checking the database for updates
        /// </summary>
        public bool IsCheckingForUpdates { get; private set; }

        /// <summary>
        /// Represents a list of fields that should be ignored during read operations
        /// </summary>
        private List<IField> IgnoreFields
        {
            get 
            { 
                return this._ignoreFields; 
            }
            set
            {
                this._ignoreFields = value;
            }
        }
        private SqlConnection Connection { get; set; }
        #endregion // Properties

        #region Constructors
        public MicrosoftSqlDriver(string connectionString)
            : base(connectionString)
        {
            IgnoreFields = new List<IField>();
        }

        public override string IdentifyDatabase()
        {
            return "Microsoft SQL Server Database";
        }
        #endregion // Constructors

        #region Public Methods
        public override bool SaveRecord(Form form, Record record) { return false; }
        public override async Task<bool> SaveRecordAsync(Form form, Record record) 
        {
            throw new NotImplementedException();
        }
        public override bool DeleteRecord(Form form, Record record) { return false; }
        public override bool SoftDeleteRecord(Form form, Record record) { return false; }
        public override void PopulateRecordCollection(DataEntryInstrument form) 
        {
            form.Records.Clear();

            string tableName = form.Title;

            using (SqlCommand command = new SqlCommand("select * from [" + tableName + "]", Connection))
            {
                using (SqlDataReader sqlReader = command.ExecuteReader())
                {
                    while (sqlReader.Read())
                    {
                        form.Records.Add(CreateRecord(form, (IDataRecord)sqlReader));
                    }
                }
            }

            //SqlDependency.Start(ConnectionString);

            //WordBuilder fieldBuilder = new WordBuilder(",");
            //foreach (Field field in form.Fields.DataFields)
            //{
            //    fieldBuilder.Append("[" + field.Name + "]");
            //}

            //using (SqlCommand command = new SqlCommand("SELECT [GlobalRecordId], " + fieldBuilder.ToString() + " FROM [" + tableName + "]",
            //    Connection))
            //{

            //    // Create a dependency and associate it with the SqlCommand.
            //    SqlDependency dependency = new SqlDependency(command);
            //    // Maintain the refence in a class member.

            //    // Subscribe to the SqlDependency event.
            //    dependency.OnChange += new
            //       OnChangeEventHandler(OnDependencyChange);

            //    // Execute the command.
            //    using (SqlDataReader reader = command.ExecuteReader())
            //    {
            //        // Process the DataReader.
            //        while (reader.Read())
            //        {
            //            form.Records.Add(CreateRecord(form, (IDataRecord)reader));
            //        }
            //    }
            //}
        }

        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
            if (disposing)
            {
                SqlDependency.Stop(ConnectionString);
            }
        }

        // Handler method
        void OnDependencyChange(object sender,
           SqlNotificationEventArgs e)
        {
            // Handle the event (for example, invalidate this cache entry).
        }

        /// <summary>
        /// Gets an observable collection of records from a table in the database
        /// </summary>
        public override ObservableCollection<Record> GetRecordCollection(string tableName)
        {
            // set up the observable collection of records
            ObservableCollection<Record> records = new ObservableCollection<Record>();

            // get the table column names and types
            DataTable schemaTable = Connection.GetSchema("COLUMNS", new string[] { null, null, tableName });

            Dictionary<string, string> columnDictionary = new Dictionary<string, string>();
            foreach (DataRow row in schemaTable.Rows)
            {
                columnDictionary.Add(row["COLUMN_NAME"].ToString(), row["DATA_TYPE"].ToString());
            }

            // set up a fake project
            Project temporaryProject = new Project();
            Form temporaryForm = new Form(temporaryProject);
            Page temporaryPage = new Page(temporaryForm);

            // map the SQL Server columns to fields in the form
            foreach (KeyValuePair<string, string> kvp in columnDictionary)
            {
                switch (kvp.Value)
                {
                    case "nvarchar": // text
                    case "char": 
                    case "varchar":
                    case "text": 
                        TextBoxField textBoxField = new TextBoxField();
                        textBoxField.Page = temporaryPage;
                        textBoxField.Form = temporaryForm;
                        textBoxField.Name = kvp.Key;
                        temporaryForm.Fields.Add(textBoxField);
                        break;
                    case "datetime":
                    case "datetime2":
                    case "date":
                        DatePickerField datePickerField = new DatePickerField();
                        datePickerField.Page = temporaryPage;
                        datePickerField.Form = temporaryForm;
                        datePickerField.Name = kvp.Key;
                        temporaryForm.Fields.Add(datePickerField);
                        break;
                    case "bigint":
                    case "numeric":
                    case "smallint":
                    case "decimal":
                    case "int":
                    case "tinyint":
                        NumberField numberField = new NumberField();
                        numberField.Page = temporaryPage;
                        numberField.Form = temporaryForm;
                        numberField.Name = kvp.Key;
                        temporaryForm.Fields.Add(numberField);
                        break;
                }
            }

            // populate the collection of records using the fake form
            SqlCommand command = new SqlCommand("select * from [" + tableName + "]", Connection);
            using (SqlDataReader sqlReader = command.ExecuteReader())
            {
                while (sqlReader.Read())
                {
                    records.Add(CreateRecord(temporaryForm, (IDataRecord)sqlReader));
                }
            }

            return records;
        }

        public override bool SaveAllRecords(Project project)
        {
            return true;
        }

        public override async Task<bool> SaveAllRecordsAsync(Project project)
        {
            return true;
        }

        /// <summary>
        /// Checks for record insertions and updates on a specific form
        /// </summary>
        /// <param name="form">The form to check for record updates</param>
        public override void CheckForUpdates(Form form)
        {
            if (form == null)
            {
                throw new ArgumentNullException("The form parameter for the CheckForUpdates method cannot be null.");
            }

            // prevent entering this method if we're already checking
            if (!IsCheckingForUpdates)
            {
                IsCheckingForUpdates = true;

                string tableName = form.Title;

                using (SqlCommand command = new SqlCommand("select * from [" + tableName + "]", Connection))
                {
                    using (SqlDataReader sqlReader = command.ExecuteReader())
                    {
                        while (sqlReader.Read())
                        {
                            #region Check for newly-added records
                            // check for new records
                            Guid guid = new Guid(sqlReader["GlobalRecordId"].ToString());
                            bool found = false;
                            foreach (Record record in form.Records)
                            {
                                if (record.Id.ToString() == guid.ToString())
                                {
                                    found = true;
                                    break;
                                }
                            }

                            if (!found)
                            {
                                form.Records.Add(CreateRecord(form, (sqlReader as IDataRecord)));
                            }
                            #endregion // Check for newly-added records

                            #region Check for updated records
                            // check for new records
                            DateTime lastSaveTime = (DateTime)(sqlReader["LastSaveTime"]);
                            foreach (Record record in form.Records)
                            {
                                if (record.Id.ToString() == guid.ToString())
                                {
                                    if (record.LastSaveTime < lastSaveTime)
                                    {
                                        foreach (IField field in form.Fields.DataFields)
                                        {
                                            string fieldName = field.Name;
                                            SetFieldValue(form, record, fieldName, (IDataRecord)(sqlReader));
                                        }
                                        break;
                                    }
                                }
                            }
                            #endregion // Check for updated records
                        }
                    }
                }

                IsCheckingForUpdates = false;
            }
        }
        #endregion // Public Methods

        #region Protected Methods
        protected override bool UpdateRecord(Form form, Record record) { return false; }
        protected override bool InsertRecord(Form form, Record record) { return false; }

        protected override void OpenConnection()
        {
            Connection = new SqlConnection(ConnectionString);
            Connection.Open();
        }

        protected override void CloseConnection()
        {
            Connection.Close();
            Connection.Dispose();
            Connection = null;
        }
        #endregion // Protected Methods

        #region Private Methods
        /// <summary>
        /// Creates a dynamic Record object from a row in the database
        /// </summary>
        /// <param name="form">The form to which the record belongs</param>
        /// <param name="dataRecord">The row in the database</param>
        /// <returns>The record observable object</returns>
        private dynamic CreateRecord(DataEntryInstrument form, IDataRecord dataRecord)
        {
            dynamic record = new Record(System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString());
            record.Id = new Guid(dataRecord["GlobalRecordId"].ToString());
            record.RecStatus = (short)(dataRecord["RecStatus"]);
            record.FirstSaveTime = (DateTime)(dataRecord["FirstSaveTime"]);
            record.LastSaveTime = (DateTime)(dataRecord["LastSaveTime"]);
            record.FirstSaveLogOn = dataRecord["FirstSaveLogOn"].ToString();
            record.LastSaveLogOn = dataRecord["LastSaveLogOn"].ToString();
            if (dataRecord["FKEY"] != null && dataRecord["FKEY"] != DBNull.Value)
            {
                record.ForeignKey = new Guid(dataRecord["FKEY"].ToString());
            }
            record.Form = form;

            foreach (IField dataField in form.Fields.DataFields)
            {
                if (!IgnoreFields.Contains(dataField))
                {
                    string fieldName = dataField.Name;
                    try
                    {
                        object value = dataRecord[fieldName];
                        SetFieldValue(form, record, fieldName, dataRecord);
                    }
                    catch (IndexOutOfRangeException)
                    {
                        // field doesn't exist, this isn't actually a problem, so ignore it
                        IgnoreFields.Add(dataField);

                        // TODO: Handle this better, we should know field names ahead of time really
                    }
                }
            }

            return record;
        }

        /// <summary>
        /// Sets the value for the record object
        /// </summary>
        /// <param name="form">The form to which the record belongs</param>
        /// <param name="record">The record whose value will be set</param>
        /// <param name="fieldName">The name of the field that will be updated in this record</param>
        /// <param name="dataRecord">The row in the database that contains the value to be assigned</param>
        private void SetFieldValue(DataEntryInstrument form, Record record, string fieldName, IDataRecord dataRecord)
        {
            #region Input Validation
            if (fieldName == null)
            {
                throw new ArgumentNullException("fieldName");
            }
            #endregion // Input Validation

            if (form.Fields.Contains(fieldName))
            {
                IField field = form.Fields[fieldName];
                if (field is IDataField)
                {
                    string fieldType = field.GetType().ToString();
                    object rawValue = dataRecord[fieldName];

                    if (rawValue != null && dataRecord[fieldName] != DBNull.Value)
                    {
                        switch (fieldType)
                        {
                            case "EpiNEXT.Core.Fields.NumberField":
                                record.AddOrUpdateFieldData(field, Convert.ToDouble(rawValue));
                                break;
                            case "EpiNEXT.Core.Fields.DatePickerField":
                                record.AddOrUpdateFieldData(field, (DateTime)rawValue);
                                break;
                            case "EpiNEXT.Core.Fields.CheckBoxField":
                                record.AddOrUpdateFieldData(field, (bool)rawValue);
                                break;
                            //case "EpiNEXT.Core.Fields.TextBoxField":
                            default:
                                record.AddOrUpdateFieldData(field, rawValue.ToString());
                                break;
                        }
                    }
                    else
                    {
                        record.AddOrUpdateFieldData(field, null);
                    }
                }
            }
        }
        #endregion // Private Methods
    }
}
